﻿/*

******************************************************

CreateDate  *  Author         *  Content

2008-08-04   *  donne.yang     *  Oracle to informix function

******************************************************

NO   *  UpdateDate   *  Author        *  Content

*********************************************************

*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections;
using System.Data.Odbc;

using FoxConn.Lib.Dbo;

namespace OracleToInformix.Source
{
    
    class Oracle2Informix
    {
        /// <summary>
        /// Oracle数据导入Informix主类
        /// </summary>
        public Oracle2Informix()
        {
        }

        /// <summary>
        /// 返回要导入的Table的所有列名信息.
        /// </summary>
        /// <param name="TableName">Table名称</param>
        /// <returns>Hashtable</returns>
        public SortedList GetColumnsInfo(string TableName)
        {
            SortedList sd = new SortedList();
            string strSQL = "select * from syscolumns where tabid =  (select tabid from systables where tabname = '" + TableName.ToLower() + "') order by colno";

            DBOperation dbo = new DBOperation(1);
            DataSet ds = dbo.GetResult(strSQL);

            if (StaticUtil.IsDsHasData(ds))
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    sd.Add(ds.Tables[0].Rows[i]["colname"].ToString(), ds.Tables[0].Rows[i]["coltype"].ToString());
                }
            }

            return sd;
        }

       /// <summary>
        /// 返回要导入的Table的所有列名信息.
       /// </summary>
       /// <param name="TableName">Table名称</param>
       /// <param name="TableColumnsName">需要查询的列名称，格式如'ColumnA','ColumnB','ColumnC'。</param>
        /// <returns>Hashtable</returns>
        public SortedList GetColumnsInfo(string TableName, string TableColumnsName)
        {
            SortedList sd = new SortedList();
            string strSQL = "select * from syscolumns where tabid =  (select tabid from systables where tabname = '" + TableName.ToLower() + "') and colname in (" + TableColumnsName + ")";

            DBOperation dbo = new DBOperation(1);
            DataSet ds = dbo.GetResult(strSQL);

            if (StaticUtil.IsDsHasData(ds))
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    sd.Add(ds.Tables[0].Rows[i]["colname"].ToString(), ds.Tables[0].Rows[i]["coltype"].ToString());
                }
            }

            return sd;

        }

        /// <summary>
        /// 初始化Insert的SQL语句.
        /// </summary>
        /// <param name="ht">某表的所有列名</param>
        /// <param name="TableName">表名</param>
        /// <returns>SQL语句</returns>
        public string InitSQLScript(SortedList sd,string TableName)
        {
            string strResult = "";
            string strColName = "";
            string strColValue = "";

            IDictionaryEnumerator ide = sd.GetEnumerator();

            while (ide.MoveNext())
            {
                strColName += "," + ide.Key;
                //strColValue += ",@" + ide.Key;
                strColValue += ",?";
            }

            strColName = strColName.Substring(1);
            strColValue = strColValue.Substring(1);

            strResult = "INSERT INTO " + TableName + "(" + strColName + ") VALUES(" + strColValue + ")";

            return strResult;
        }

        /// <summary>
        /// 初始化OdbcParameter.
        /// </summary>
        /// <param name="ht">某表的所有列名</param>
        /// <returns>OracleParameter[]</returns>
        public OdbcParameter[] InitOracleParameter(SortedList sd)
        {
            int i = 0;

            IDictionaryEnumerator ide = sd.GetEnumerator();
            OdbcParameter[] Parameters = new OdbcParameter[sd.Count];

            while (ide.MoveNext())
            {
                Parameters[i] = new OdbcParameter(ide.Key.ToString(), GetOdbcType(ide.Value.ToString()));
                i++;
            }

            return Parameters;
        }

        /// <summary>
        /// 返回OracleType.
        /// </summary>
        /// <param name="DbType">字段类型</param>
        /// <returns>OracleType</returns>
        public OdbcType GetOdbcType(string DbType)
        {
            OdbcType odbcType = OdbcType.VarChar;

            switch (DbType.ToUpper())
            {
                case "0": odbcType = OdbcType.Char; break;
                case "1": odbcType = OdbcType.SmallInt; break;
                case "2": odbcType = OdbcType.Int; break;
                case "4": odbcType = OdbcType.Real; break;
                case "5": odbcType = OdbcType.Decimal; break;
                case "7": odbcType = OdbcType.Date; break;
                case "10": odbcType = OdbcType.DateTime; break;
                case "13": odbcType = OdbcType.VarChar; break;
                case "15": odbcType = OdbcType.NChar; break;
                case "16": odbcType = OdbcType.NVarChar; break;


                //not null
                case "256": odbcType = OdbcType.Char; break;
                case "257": odbcType = OdbcType.SmallInt; break;
                case "258": odbcType = OdbcType.Int; break;
                case "260": odbcType = OdbcType.Real; break;
                case "261": odbcType = OdbcType.Decimal; break;
                case "263": odbcType = OdbcType.Date; break;
                case "266": odbcType = OdbcType.DateTime; break;
                case "269": odbcType = OdbcType.VarChar; break;
                case "271": odbcType = OdbcType.NChar; break;
                case "272": odbcType = OdbcType.NVarChar; break;
                default: odbcType = OdbcType.VarChar; break;
            }

            return odbcType;
        }
    }
}
